package mwa.cs.dao;

import java.util.List;
import java.util.Map;
import mwa.cs.database.Database;
import mwa.cs.model.RcTransD;

public class RcTransDDAO {

    /** a database of this RC_TRANS_D */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param db a database
     */
    public RcTransDDAO(Database db) {
        this.db = db;
    }
    
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM rc_trans_d";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    
    public void insertRcTransD(RcTransD poRcTansD) {
        String vaSql = "INSERT INTO rc_trans_d (site_id, serial_number, seq, bill_number, receive_code, vat_amount, bill_amount, "
        		+ " water_charge, water_fee, garbage_charge, wastewater_charge, other_charge, insert_user_id, insert_date, insert_time)  "
        		+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        try {
            db.insert(vaSql , poRcTansD.getVnSiteId()
            		, poRcTansD.getVnSerialNumber()
            		, poRcTansD.getVnSeqNumber()
            		, poRcTansD.getVaBillNumber()
            		, poRcTansD.getVaReceiveCode()
            		, poRcTansD.getVnVatAmount()
            		, poRcTansD.getVnBillAmount()
            		, poRcTansD.getVnWaterCharge()
            		, poRcTansD.getVnWaterFee()
            		, poRcTansD.getVnGarbageCharge()
            		, poRcTansD.getVnWasteWaterCharge()
            		, poRcTansD.getVnOtherCharge()
            		, poRcTansD.getVnInsertUserId()
            		, poRcTansD.getVaInsertDate()
            		, poRcTansD.getVaInsertTime());
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
 // ค้นหาข้อมูล Rc_Trans_D หน้ายกเลิกใบเสร็จรับเงิน
  	public List<Map<String, Object>> searchRcTransDForCancel(long paSerialNumber) {

  		String vaSql = "SELECT rtd.seq, rc.description, SUM(bill_amount) - SUM(vat_amount) AS total_amount, "
  				+ "SUM(vat_amount) AS vat_amount, SUM(bill_amount) AS bill_amount "
  				+ "FROM rc_trans_d rtd "
  				+ "INNER JOIN receive_code rc ON rtd.receive_code = rc.receive_code "
  				+ "WHERE rtd.serial_number = ? "
  				+ "GROUP BY rtd.seq, rc.description "
  				+ "ORDER BY rtd.seq ASC";
  		List<Map<String, Object>> result = db.queryList(vaSql, paSerialNumber);
  		
  		return result;
  	}
  	
 // ค้นหาข้อมูล Rc_Trans_D หน้ายกเลิกใบเสร็จรับเงิน เพื่อเช็ค reset ตาราง Ar หรือ Request
   	public Map<String, Object> searchRcTransDForResetData(long paSerialNumber) {

   		String vaSql = "SELECT bill_number "
   				+ "FROM rc_trans_d "
   				+ "WHERE serial_number = ? AND receive_code <> '21' "
   				+ "GROUP BY bill_number";
   		Map<String, Object> result = db.querySingle(vaSql, paSerialNumber);
   		if (result != null) {
        	return result;
        } else {
            return null;
        }
   	}
  	
	public List<Map<String, Object>> findRcTransDBySerialNumber(int pnSiteId, String paSerialNumber) {

  		String vaSql = "SELECT rc_trans_d.receive_code,rc_trans_d.serial_number,rc_trans_d.seq,receive_code.description,"
  				+ "rc_trans_d.water_charge,rc_trans_d.water_fee,rc_trans_d.vat_amount,"
  				+ "rc_trans_d.garbage_charge,rc_trans_d.wastewater_charge,rc_trans_d.other_charge,"
  				+ "rc_trans_d.bill_amount,rc_trans_m.penalty FROM rc_trans_d, receive_code, rc_trans_m "
  				+ "WHERE rc_trans_d.site_id = "+pnSiteId+" AND receive_code.receive_code = rc_trans_d.receive_code "
  				+ "AND rc_trans_d.serial_number = rc_trans_m.serial_number "
  				+ "AND rc_trans_d.serial_number = "+paSerialNumber+" ORDER BY seq asc";
  		List<Map<String, Object>> result = db.queryList(vaSql);
  		
  		return result;
  	}
    
	// บันทึกข้อมูล RcTransD สำหรับรับเงินค่าอื่นๆ
  	public boolean insertRcTransDForReceiveOther(RcTransD poNewRcTransD, String paRequestNumber) {
          String vaSql = "INSERT INTO rc_trans_d ( "
        		  + "site_id, serial_number, seq, bill_number, receive_code, water_charge, water_fee, vat_amount, "
        		  + "wastewater_charge, garbage_charge, other_charge, bill_amount, insert_user_id, insert_date, insert_time, update_user_id) "
        		  + "SELECT ?, ?, ROWNUM, ?, receive_code, ?, ?, vat_amount, ?, ?, ?, total_amount, ?, ?, ?, ? "
        		  + "FROM request_quote WHERE request_number = ?";
        
          boolean result = false;
          try {
          	db.update(vaSql,
          			poNewRcTransD.getVnSiteId(),
          			poNewRcTransD.getVnSerialNumber(),
          			poNewRcTransD.getVaBillNumber(),
          			poNewRcTransD.getVnWaterCharge(),
          			poNewRcTransD.getVnWaterFee(),
          			poNewRcTransD.getVnWasteWaterCharge(),
          			poNewRcTransD.getVnGarbageCharge(),
          			poNewRcTransD.getVnOtherCharge(),
          			poNewRcTransD.getVnInsertUserId(),
          			poNewRcTransD.getVaInsertDate(),
          			poNewRcTransD.getVaInsertTime(),
          			poNewRcTransD.getVnUpdateUserId(),
          			paRequestNumber);
          	result = true;
          } catch (Exception e) {
          	System.out.println(e);
          	result = false;
          }
          return result;
  	}
  	
  	
 	public boolean deleteRcTransDForCancelRecieveBill(int pnSiteId,String paSereialNumber){
 		boolean result;
 		try{
 		String vaSql = " " +
 				"DELETE RC_TRANS_D " +
 				"WHERE SITE_ID = '"+pnSiteId+"' " +
 				"AND SERIAL_NUMBER = '"+paSereialNumber+"' ";
 		
 		db.update(vaSql);
 		result = true;
 		}
 		catch (Exception e) {
 			result = false;
		}
 		
 		System.out.println("Satus deleteRcTransD : "+result);
 		
		return result;
 		
 	}
	
}
